package com.kiwi.util.excel;

/**
 * Created by Administrator on 2018/4/16.
 */

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * jxl读excel
 *
 * @author hancongcong
 *
 */
public class JxlReader
{
    /**
     *
     * @param pathName---excel路径
     * @param sheetName---工作表名称
     * @return  所有的excel参数
     * @throws BiffException
     * @throws IOException
     */
    public static ArrayList<HashMap<String,String>> jxlReader(String pathName,String sheetName) throws BiffException, IOException{
        ArrayList<HashMap<String,String>> cases = new ArrayList<HashMap<String,String>>();


        //打开excle
        File xlsFile = new File(pathName);
        //打开workbook
        Workbook workbook = Workbook.getWorkbook(xlsFile);
        //获得工作表
        Sheet sheet = workbook.getSheet(sheetName);
        // 获得行数
        int rows = sheet.getRows();
        // 获得列数
        int cols = sheet.getColumns();
        // 读取数据
        for (int row = 1; row < rows; row++) {
            HashMap<String,String> params = new HashMap<String,String>();
            for (int col = 0; col < cols; col++) {
                //表头和值放入hashmap
                params.put(sheet.getCell(col, 0).getContents(),sheet.getCell(col, row).getContents());
                System.out.printf("%15s", sheet.getCell(col, row).getContents());
            }
            System.out.println();
            cases.add(params);
        }
        workbook.close();
        return  cases;

    }

    public static void main(String[] args) throws BiffException, IOException
    {
        ArrayList<HashMap<String,String>> test = jxlReader("C:\\Users\\Administrator\\Desktop\\test.xls","Test0");
        for(int i = 0;i<test.size();i++){
            System.out.println(test.get(i).size());
            System.out.println(test.get(i));
        }
       /* File xlsFile = new File("C:\\Users\\Administrator\\Desktop\\test.xls");
        // 获得工作簿对象
        Workbook workbook = Workbook.getWorkbook(xlsFile);
        // 获得所有工作表
        Sheet[] sheets = workbook.getSheets();
        // 遍历工作表
        if (sheets != null)
        {
            for (Sheet sheet : sheets)
            {                // 获得行数
                int rows = sheet.getRows();
                // 获得列数
                int cols = sheet.getColumns();
                // 读取数据
                for (int row = 0; row < rows; row++)
                {
                    for (int col = 0; col < cols; col++)
                    {
                        System.out.printf("%10s", sheet.getCell(col, row)
                                .getContents());
                    }
                    System.out.println();
                }
            }
        }
        workbook.close();*/
    }
}